package com.vogue.supplymall.shop.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.SearchInfo;
import org.apache.commons.lang3.StringUtils;

import java.util.Arrays;
import java.util.Map;

/**
 * The type Product sql provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-03
 */
public class RelevanceProductSqlProvider extends BaseSqlProvider {

    /**
     * Find string.
     * 商品检索sql
     *
     * @param searchInfo the search info
     * @return the string
     */
    public String find(SearchInfo searchInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(selectTwo(searchInfo));
        // 根据前端传的开始结束位置进行分页检索
        sql.append(" LIMIT #{start},#{length}");
        return DealStatus.formatShadeSql(sql.toString());
    }

    private String selectTwo(SearchInfo searchInfo) {
        StringBuilder sql = new StringBuilder();
        Map positions = searchInfo.getPositions();
        sql.append(getSelectPart());
        sql.append(" FROM ")
                .append(" relevance r ")
                /*匹配关注收藏关系表*/
                .append(" LEFT JOIN product p ON (p.id = r.targetobjid)")
                .append(" LEFT JOIN rel_product_code rpc2 ON ( ")
                .append(" p.id = rpc2.productid ");
        // 用户匹配
        if (null != searchInfo.getSyscodeid()) {
            sql.append(" AND rpc2.syscodeid = #{syscodeid} ");
        }
        sql.append(" ) ")
                .append(" LEFT JOIN shop s ON (p.shopid = s.id and s.stopflag = 0) ")
                .append(" LEFT JOIN rel_product_category rpc ON (p.id = rpc.productid) ")
                .append(" LEFT JOIN base_category bc ON (rpc.categoryid = bc.id) ")
                .append(" LEFT JOIN product_img pi ON (p.id = pi.productid AND pi.listflag = 1) ")
                .append(" LEFT JOIN product_attrplan pa ON (p.id = pa.productid AND pa.disabledflag = 0 AND pa.defaultflag = 1 ) ")
                .append(" LEFT JOIN product_attrplan_detail pad ON (pa.id = pad.attrplanid) ")
                .append(" LEFT JOIN product_attrvalue pav ON (pad.attrvalueid = pav.id) ")
                .append(" LEFT JOIN product_price pp1 ON ( ")
                .append(" p.id = pp1.productid ")
                .append(" AND pa.id = pp1.attrplanid ")
                .append(" AND pp1.allflag = 1) ")
                .append(" LEFT JOIN product_price pp2 ON ( ")
                .append(" p.id = pp2.productid ")
                .append(" AND pa.id = pp2.attrplanid ")
                .append(" AND pp2.allflag = 0 ");
        // 用户匹配
        if (null != searchInfo.getSyscodeid()) {
            sql.append(" AND pp2.oneid = #{syscodeid} ")
                    // 2018/2/26 匹配时间区间允许的私有价格
                    .append(" AND pp2.begindate < NOW() ")
                    .append(" AND pp2.enddate > NOW() ");
        } else {
            sql.append(" AND pp2.oneid = -1 ");
        }
        sql.append(" ) ");
        sql.append(getWherePart(searchInfo, positions, 1));
        sql.append(" GROUP BY ")
                .append(" r.id ");
        sql.append(" ORDER BY disabledflag asc, r.createdate desc ");
        return sql.toString();
    }

    private String getSelectPart() {
        StringBuilder select = new StringBuilder();
        select.append(" SELECT ")
                .append(" p.id, ")
                .append(" p.syscodeid, ")
                .append(" p.shopid, ")
                .append(" p.`name` pname, ")
                .append(" p.detailinfo, ")
                .append(" p.itemlabel, ")
                .append(" s.`name` sname, ")
                .append(" s.entname, ")
                .append(" bc.`name` bcname, ")
                .append(" pi.url, ")
                .append(" p.sendlvl, ")
                /*关注收藏表的id*/
                .append(" IF(s.disabledflag = 0,if(p.disabledflag = 0,if(pa.disabledflag = 0,0,1),1),1) disabledflag, ")
                .append(" r.id rid, ")
                .append(" IF ( p.sendlvl = 1, 'all',  IF ( p.sendlvl = 2, s.area,  IF ( p.sendlvl = 4, s.province,  IF (p.sendlvl = 8, s.city, 0) ) ) ) area,  ")
                .append(" p.hiddenflag, ")
                .append(" pa.inventory, ")
                .append(" pa.weight, ")
                .append(" pav.`name` attr_name, ")
                .append(" pav.typeid, ")
                .append(" rpc.categoryid, ")
                .append(" pp1.price public_price, ")
                .append(" pp2.price private_price ");
        return select.toString();
    }

    private String getWherePart(SearchInfo searchInfo, Map positions, int hiddenflag) {
        StringBuilder where = new StringBuilder();
        where.append(" WHERE ")
                .append(" r.originobjid = #{loginId} ")
                .append(" AND r.type = #{type} ")
                .append(" AND r.targetobjtype = #{targetObjType} ")
                .append(" AND r.disabledflag = 0 ");
        // 进店检索商品时匹配
        if (null != searchInfo.getId()) {
            where.append(" AND s.id = #{id} ");
        }

        // 匹配区域省市
//        switch (searchInfo.getAreaFlag()) {
//            case 1:
//                // 默认匹配可配送
//                if (null != positions) {
//                    where.append(" AND ( ")
//                            .append(" (p.sendlvl = 2 AND s.area = '")
//                            .append(positions.get("2"))
//                            .append("') OR ( ")
//                            .append(" p.sendlvl = 4 ")
//                            .append(" AND s.province = ")
//                            .append(positions.get("4"))
//                            .append(" ) OR ( ")
//                            .append(" p.sendlvl = 8 ")
//                            .append(" AND s.city = #{position} ")
//                            .append(" ) OR (p.sendlvl = 1) ")
//                            .append(" ) ");
//                }
//                break;
//            case 2:
//                /* 匹配同城 */
//                where.append("AND p.sendlvl > 0 AND s.city = #{position} ");
//                break;
//            default:
//                // 匹配全部
////            where.append(" AND p.sendlvl = 1 ");
//                break;
//        }

        // 匹配搜索内容
        if (StringUtils.isNotEmpty(searchInfo.getContent())) {
//            where.append(" AND (  p.`name` LIKE CONCAT('%',#{content},'%') ")
//                    .append(" OR bc.`name` LIKE CONCAT('%',#{content},'%') ")
//                    .append(" ) ");
            where.append(" AND  p.`name` LIKE CONCAT('%',#{content},'%') ");
        }

        // 匹配最低价格
        if (StringUtils.isNotEmpty(searchInfo.getPriceUp())) {
            where.append(" AND pp1.price >= #{priceUp} ");
        }
        // 匹配最高价格
        if (StringUtils.isNotEmpty(searchInfo.getPriceDown())) {
            where.append(" AND pp1.price <= #{priceDown} ");
        }
        // 匹配类别
        if (StringUtils.isNotEmpty(searchInfo.getCategoryCode())) {
            if (searchInfo.getCategoryCode().contains(",")) {
                searchInfo.setCategorys(searchInfo.getCategoryCode().split(","));
            }
            if (null != searchInfo.getCategorys()) {
                where.append(" AND bc.id in ( ");
                Arrays.stream(searchInfo.getCategorys()).map(s -> s + ",").forEach(where::append);
                where.append(" 0 ) ");
            } else {
                where.append(" AND bc.id = #{categoryCode} ");
            }
        }
        // 匹配库存
        if (searchInfo.getInventoryFlag() == 1) {
            where.append(" AND pa.inventory > 0 ");
        }
        return where.toString();
    }
}
